
****************************************************************************
**Win or Lose: Residential Sorting After a School Choice Lottery
*By Andrew Bibler & Stephen B Billings

****************************************************************************

clear all 
cap log close
set more off
set matsize 800 

use CMS_Lot_Choices.dta, clear

keep year dl litd mont ib lead arts trad accel open magnet choicenumber school 

gen appch1_link = choicenumber 
gen appch1schl = school 
foreach x in dl litd mont ib lead arts trad accel open magnet school { 
rename `x' appch1`x'   
	} 
tempfile app1choices
		save `app1choices'
		
rename appch1_link assch_link 
rename appch1schl assschl 
foreach x in dl litd mont ib lead arts trad accel open magnet school { 
rename appch1`x' assch`x' 
	} 
tempfile asschoices
		save `asschoices'
 
** home price data
use house_price_resid3.dta , clear

gen year = salesyear + 1
	
keep year salesyear Elemschoolcode Elemschoolname Elem_price_resid Elem_price_residV2 

duplicates drop 

rename Elemschoolcode homeschl

xtset homeschl year

gen elem_price_resid_ch_hs = Elem_price_resid - L.Elem_price_resid
gen elem_price_residV2_ch_hs = Elem_price_residV2 - L.Elem_price_residV2

gen elem_price_resid_ch2_hs = F.Elem_price_resid - Elem_price_resid
gen elem_price_residV2_ch2_hs = F.Elem_price_residV2 - Elem_price_residV2

keep homeschl year salesyear elem_price_resid_ch_hs elem_price_resid_ch2_hs elem_price_residV2_ch_hs elem_price_residV2_ch2_hs 

tempfile elemch_hs
	save `elemch_hs' 
	
rename homeschl home_lead1 

foreach v in elem_price_resid_ch elem_price_resid_ch2 elem_price_residV2_ch elem_price_residV2_ch2 {
rename `v'_hs `v'_hsld
	}
	
tempfile elemch_hsld 
	save `elemch_hsld' 
	 

** cms data
use "CMS_Clean_Long.dta" , clear
 
merge m:1 appch1_link year using `app1choices' , gen(app1merge)

keep if (app1merge==1 | app1merge==3)

merge m:1 mastid using frl_mb3.dta , gen(frl_mb3merge)    

keep if (frl_mb3merge==1 | frl_mb3merge==3)    
      
gen testgrade = grade  
keep if grade!=.

gen mbyear = year 

merge 1:1 mastid testgrade mbyear using nc_chars.dta , gen(mbmerge)    

keep if (mbmerge==1 | mbmerge==3)  

gen exit = (mbmerge==1)  
 
xtset mastid grade 

gen home_lead1 = F.homeschl 

gen home_lead1_miss = (home_lead1==.) 

gen home_lead2 = F2.homeschl
gen home_lead2_miss = (home_lead2==.)

gen curr_lead1 = F.currschool

gen curr_lead1_miss = (curr_lead1==.) 

gen curr_lead2 = F2.currschool
gen curr_lead2_miss = (curr_lead2==.) 

replace sciencestand=0 if year<2008
replace sci_grlevel=1 if year<2008

gen math_lag = L.mathstand 
gen math_lag2 = L2.mathstand

gen read_lag = L.readstand 
gen read_lag2 = L2.readstand	

gen science_lag = L.sciencestand 
gen science_lag2 = L2.sciencestand

gen frlmb_lag = L.frl_mb
gen frlmb_lag2 = L2.frl_mb

gen lep_Y_lag = L.lep_Y
gen lep_Y_lag2 = L2.lep_Y

gen math_gr_lag = L.math_grlevel
gen math_gr_lag2 = L2.math_grlevel	

gen read_gr_lag = L.read_grlevel
gen read_gr_lag2 = L2.read_grlevel

gen science_gr_lag = L.sci_grlevel
gen science_gr_lag2 = L2.sci_grlevel

gen mardgr_int_lag = math_gr_lag*read_gr_lag
gen mardgr_int_lag2 = math_gr_lag2*read_gr_lag2

gen allgr_int_lag = math_gr_lag*read_gr_lag*science_gr_lag
gen allgr_int_lag2 = math_gr_lag2*read_gr_lag2*science_gr_lag2

gen ma_aig_lag = L.ma_aig
gen ma_aig_lag2 = L2.ma_aig

gen rd_aig_lag = L.rd_aig
gen rd_aig_lag2 = L2.rd_aig

gen aig_int_lag = rd_aig_lag*ma_aig_lag
gen aig_int_lag2 = rd_aig_lag2*ma_aig_lag2

keep if ((grade==0 | grade==6) & homeschl!=. & appch1school!=. & homeschl!=appch1school & appch1!=0 & placementtype!="S")

gen app2 = (appch2!=0) 
gen app3 = (appch3!=0)

egen wonany = rowmax(wonfirst wonsecond wonthird)

foreach l in 1 2 {
	egen move`l' = diff(homeschl home_lead`l')
	egen attdiff`l' = diff(homeschl curr_lead`l')
	gen atthome`l' = (1 - attdiff`l')
	}

gen changehs = (move1==1 & home_lead1!=.)

gen changehs2 = (move2==1 & home_lead2!=.) 
 
su frpl if grade==0 
replace frpl = `r(mean)' if (grade==0 & year>2010)

su frpl if grade==6 
replace frpl = `r(mean)' if (grade==6 & year>2010)

foreach y in 07 08 09 10 11 12 13 {
	gen frpl`y' = frpl if year==20`y'
	replace frpl`y' = 0 if year!=20`y'
	}
	
gen lunch = frpl if year<=2010

replace lunch = frlmb_lag if (year>2010 & grade==6) 	

replace lunch = frl_mb3 if (year>2010 & grade==0) 

gen lunch_miss = (lunch==.)

foreach g in 0 6 {	
foreach y in 11 12 13 {
	su lunch if ( year==20`y' & grade==`g' )
	replace lunch=`r(mean)' if (lunch==. & year==20`y' & grade==`g' )
	}
	}
	
foreach y in 07 08 09 10 11 12 13 {
	gen lunch`y' = lunch if year==20`y'
	replace lunch`y' = 0 if year!=20`y'
	}

gen ksample = (grade==0 & year>2006)

gen sixsample = (grade==6 & cms!=. & charter!=. & exit!=. & math_lag!=. & read_lag!=. & science_lag!=. & lunch!=. & math_gr_lag!=. & read_gr_lag!=. & science_gr_lag!=. & ma_aig_lag!=. & rd_aig_lag!=.)

keep if (ksample==1 | sixsample==1) 

** kindergarten 
replace appch1lead=1 if (ksample==1 & appch1magnet==0 & appch1==166)
gen appch1stem =(ksample==1 & appch1magnet==0 & appch1==180)
gen appch1nclb =(ksample==1 & appch1magnet==0 & inlist(appch1, 190, 250, 604))
replace appch1nclb=1 if (ksample==1 & appch1magnet==0 & appch1==602 & year==2010)
replace appch1nclb=1 if (ksample==1 & appch1magnet==0 & appch1==613 & (year==2011 | year==2013))

** sixth
replace appch1dl=1 if (sixsample==1 & appch1magnet==0 & appch1==154)
replace appch1stem=1 if (sixsample==1 & appch1magnet==0 & (appch1==180 | appch1==181))
replace appch1nclb=1 if (sixsample==1 & appch1magnet==0 & inlist(appch1, 187, 194, 245, 248, 601, 612))

replace appch1magnet=1 if (appch1lead==1) 
replace appch1magnet=1 if (appch1stem==1)
replace appch1magnet=1 if (appch1dl==1)

gen appch1other = (appch1magnet==0 & appch1nclb==0)
				
gen meetreq = .
replace meetreq=1 if ksample==1

replace meetreq=allgr_int_lag if appch1stem==1 & (sixsample==1)
replace meetreq=mardgr_int_lag if appch1ib==1 & (sixsample==1)
replace meetreq=read_gr_lag if appch1dl==1 & (sixsample==1) 

replace meetreq=1 if meetreq==. & (sixsample==1)

keep if meetreq==1

gen lowread_nonmag = (1 - appch1magnet)*(1 - read_gr_lag) 

** Sample 
drop if (sixsample==1) & (appch1lead==1 | appch1arts==1)  

drop if (year<=2008 & (sixsample==1)

drop if (year<=2006 & ksample==1)

egen lottery = group(grade appch1 year)

bysort lottery: egen winprob=mean(wonfirst)
keep if winprob>0 & winprob<1 

foreach y in 07 08 09 10 11 12 13 {
	gen anyt1`y' = anytitleone if year==20`y' 
	replace anyt1`y' = 0 if year!=20`y' 
	}
	
foreach y in 07 08 09 10 11 12 13 {
	gen anyt1lunch`y' = anyt1`y'*lunch`y' 
	}
	
foreach y in 07 08 09 10 11 12 13 {
	gen esly`y' = eslyes if year==20`y' 
	replace esly`y' = 0 if year!=20`y' 
	}

merge m:1 mastid using malefem.dta , gen(merge_sex) 
keep if (merge_sex==3) 

xtset lottery

tempfile all
	save `all'
	
save moveprob-diffschool-over.dta , replace	
	
** scores
	
gen assch_link = asschoice

merge m:1 assch_link year using `asschoices' , gen(merge_assch)

keep if inlist(merge_assch , 1 , 3)

merge m:1 assschl using "scores-assigned.dta" , gen(merge_assscores)

keep if inlist(merge_assscores , 1 , 3)

merge m:1 curr_lead1 using "scores-attended.dta" , gen(merge_attscores)

keep if inlist(merge_attscores , 1 , 3)

		
save student-lot-mov-scores.dta , replace 

use student-lot-mov-scores.dta , clear


** kindergarten

use house_price_resid2_rev.dta, clear

keep Elem* 

drop ElembyCBG*

duplicates drop 

tempfile elem
	save `elem' 

rename Elemschoolcode homeschl
rename Elem_price_resid Elem_price_resid_hs
rename Elem_price_residV2 Elem_price_residV2_hs

tempfile elem_hs
	save `elem_hs' 
	
rename homeschl home_lead1 

foreach v in Elem_price_resid Elem_price_residV2 {
rename `v'_hs `v'_hsld
	}
	
tempfile elem_hsld 
	save `elem_hsld' 
	
rename home_lead1 home_lead2 

foreach v in Elem_price_resid Elem_price_residV2 {
rename `v'_hsld `v'_hsld2
	}
	
tempfile elem_hsld2 
	save `elem_hsld2' 
	
	
use student-lot-mov-scores.dta, clear

keep if ksample==1

merge m:1 homeschl using `elem_hs' , gen(merge_hshousing)

keep if inlist(merge_hshousing , 1 , 3)

merge m:1 home_lead1 using `elem_hsld' , gen(merge_hsldhousing)

keep if inlist(merge_hsldhousing , 1 , 3)

** 2 year lead
merge m:1 home_lead2 using `elem_hsld2' , gen(merge_hsld2housing)

keep if inlist(merge_hsld2housing , 1 , 3)

gen elem_price_resid_diff = Elem_price_resid_hsld - Elem_price_resid_hs
gen elem_price_residV2_diff = Elem_price_residV2_hsld - Elem_price_residV2_hs

gen elem_price_resid_diff2 = Elem_price_resid_hsld2 - Elem_price_resid_hs
gen elem_price_residV2_diff2 = Elem_price_residV2_hsld2 - Elem_price_residV2_hs


** annual resids

merge m:1 homeschl year using `elemch_hs' , gen(merge_hshousing_ch)

keep if inlist(merge_hshousing_ch , 1 , 3)

merge m:1 home_lead1 year using `elemch_hsld' , gen(merge_hsldhousing_ch)

keep if inlist(merge_hsldhousing_ch , 1 , 3)


egen yrhs = group(year homeschl)

global lotvars "move1 female black white hispanic frpl wonany app2 app3"  

qui xtreg mathstand_as wonfirst $lotvars  i.year#homeschl , fe cluster(lottery) 
gen scoresample = e(sample) 

qui xtreg mathstand_at wonfirst $lotvars  i.year#homeschl , fe cluster(lottery) 
gen atscoresample = e(sample) 

qui xtreg elem_price_resid_diff wonfirst $lotvars i.yrhs , fe cluster(lottery)
gen pricesample = e(sample) 

gen attriter = (scoresample==0 | atscoresample==0 | pricesample==0) 
gen stayer = (scoresample==1 & atscoresample==1 & pricesample==1)

tempfile kanalysis 
	save `kanalysis' 
	
	
** CBG locations

use id_add_bg.dta , clear

rename year_r year

merge m:1 geo_addrid using geo_ref_95_09.dta , gen(mergecbg2000)

keep if inlist(mergecbg2000 , 1 , 3)

gen blockgroup_trunc = regexs(1) if regexm(blockgroup , "([0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9])([0-9])")
replace blockgroup_trunc = blockgroup if blockgroup_trunc==""

gen cbg = blockgroup_trunc 

gen blockgroup_trunc_r = real(blockgroup_trunc) 

gen linkpre = 2000 if year<=2010
replace linkpre = 2010 if year>=2011

gen linkatt = 2000 if year<=2009
replace linkatt = 2010 if year>=2010 

gen linkatt2 = 2010

xtset mastid year 
sort mastid year

gen cbgpre = cbg[_n-1] if (year == (year[_n-1] + 1) ) & (mastid == mastid[_n-1])  

gen cbg2 = cbg[_n+1] if (year == (year[_n+1] - 1) ) & ( mastid == mastid[_n+1] ) 

keep mastid linkatt linkatt2 linkpre year cbg cbg2 cbgpre

tempfile bgatt
	save `bgatt' 
	

** middle school

use house_price_resid3.dta , clear

gen year = salesyear + 1
	
keep year salesyear Midschoolcode Midschoolname Mid_price_resid Mid_price_residV2 

duplicates drop 

rename Midschoolcode homeschl

xtset homeschl year

gen mid_price_resid_ch_hs = Mid_price_resid - L.Mid_price_resid
gen mid_price_residV2_ch_hs = Mid_price_residV2 - L.Mid_price_residV2

gen mid_price_resid_ch2_hs = F.Mid_price_resid - Mid_price_resid
gen mid_price_residV2_ch2_hs = F.Mid_price_residV2 - Mid_price_residV2

keep homeschl year salesyear mid_price_resid_ch_hs mid_price_resid_ch2_hs mid_price_residV2_ch_hs mid_price_residV2_ch2_hs

tempfile midch_hs
	save `midch_hs' 
	
rename homeschl home_lead1 

foreach v in mid_price_resid_ch mid_price_resid_ch2 mid_price_residV2_ch mid_price_residV2_ch2 {
rename `v'_hs `v'_hsld
	}
	
tempfile midch_hsld 
	save `midch_hsld' 
	 	
use house_price_resid2_rev.dta, clear

keep Mid* 

drop MidbyCBG*

duplicates drop 

tempfile six
	save `six' 

rename Midschoolcode homeschl
rename Mid_price_resid Mid_price_resid_hs
rename Mid_price_residV2 Mid_price_residV2_hs

tempfile six_hs
	save `six_hs' 
	
rename homeschl home_lead1 

foreach v in Mid_price_resid Mid_price_residV2 {
rename `v'_hs `v'_hsld
	}
	
tempfile six_hsld 
	save `six_hsld' 
	
rename home_lead1 home_lead2	

foreach v in Mid_price_resid Mid_price_residV2 {
rename `v'_hsld `v'_hsld2
	}
	
tempfile six_hsld2 
	save `six_hsld2' 	
	
use house_price_resid2_rev.dta, clear
gen linkatt = 2000
gen linkpre = 2000
gen linkatt2 = 2000

append using house_price_resid2_2010_rev.dta 
replace linkatt=2010 if linkatt==.
replace linkpre=2010 if linkpre==.
replace linkatt2=2010 if linkatt2==.

gen cbgpre = f2000
replace cbgpre = f2010 if linkatt==2010

drop if cbgpre==""

keep cbgpre linkatt linkatt2 linkpre Midschoolcode Midby*
rename Midschoolcode homeschl

duplicates drop

tempfile sixbycbg
	save `sixbycbg' 

rename MidbyCBG_price_resid MidbyCBG_price_resid_hs
rename MidbyCBG_price_residV2 MidbyCBG_price_residV2_hs


tempfile sixbycbg_hs 
	save `sixbycbg_hs' 
	
rename homeschl home_lead1 
rename cbgpre cbg

foreach v in MidbyCBG_price_resid MidbyCBG_price_residV2 {
rename `v'_hs `v'_hsld
	}
		
tempfile sixbycbg_hsld 
	save `sixbycbg_hsld' 
	
rename home_lead1 home_lead2 
rename cbg cbg2

foreach v in MidbyCBG_price_resid MidbyCBG_price_residV2 {
rename `v'_hsld `v'_hsld2
	}
	
tempfile sixbycbg_hsld2 
	save `sixbycbg_hsld2' 
	

** yearly resids
	
use house_price_resid3.dta, clear
	
keep Midschoolcode Mid_price_resid salesyear	
duplicates drop
drop if Midschoolcode==.
tempfile yrmerge
	save `yrmerge' 
	
use house_price_resid3_2010.dta 
gen linkatt=2010
gen linkpre=2010

merge m:1 Midschoolcode Mid_price_resid using `yrmerge'

** 2000 CBGs
append using house_price_resid3.dta 

replace linkatt=2000 if linkatt==.
replace linkpre=2000 if linkpre==.

** 
gen cbgpre = f2000
replace cbgpre = f2010 if linkatt==2010

drop if cbgpre==""

gen year = salesyear + 1
	
keep year salesyear cbgpre linkatt linkpre Midschoolcode Midby*

duplicates drop

rename Midschoolcode homeschl

egen schl_cbg = group(homeschl cbgpre linkatt)

xtset schl_cbg year

gen midbyCBG_price_resid_ch_hs = MidbyCBG_price_resid - L.MidbyCBG_price_resid
gen midbyCBG_price_residV2_ch_hs = MidbyCBG_price_residV2 - L.MidbyCBG_price_residV2

gen midbyCBG_price_resid_ch2_hs = F.MidbyCBG_price_resid - MidbyCBG_price_resid
gen midbyCBG_price_residV2_ch2_hs = F.MidbyCBG_price_residV2 - MidbyCBG_price_residV2

keep homeschl cbgpre year linkatt linkpre salesyear midbyCBG_price_resid_ch_hs midbyCBG_price_resid_ch2_hs midbyCBG_price_residV2_ch_hs midbyCBG_price_residV2_ch2_hs 

tempfile sixbycbgch_hs 
	save `sixbycbgch_hs' 
	
rename homeschl home_lead1 
rename cbgpre cbg

foreach v in midbyCBG_price_resid_ch midbyCBG_price_resid_ch2 midbyCBG_price_residV2_ch midbyCBG_price_residV2_ch2 {
rename `v'_hs `v'_hsld
	}
		
tempfile sixbycbgch_hsld 
	save `sixbycbgch_hsld'  
	
	
********************************************************************************	
	
use student-lot-mov-scores.dta, clear

keep if sixsample==1


merge m:1 homeschl using `six_hs' , gen(merge_price)

keep if inlist(merge_price , 1 , 3)

merge m:1 home_lead1 using `six_hsld' , gen(merge_priceld)

keep if inlist(merge_priceld , 1 , 3)

merge m:1 home_lead2 using `six_hsld2' , gen(merge_priceld2)

keep if inlist(merge_priceld2 , 1 , 3)


** merge resids

merge m:1 homeschl year using `midch_hs' , gen(merge_hshousing_ch)

keep if inlist(merge_hshousing_ch , 1 , 3)

merge m:1 home_lead1 year using `midch_hsld' , gen(merge_hsldhousing_ch)

keep if inlist(merge_hsldhousing_ch , 1 , 3)

merge 1:1 mastid year using `bgatt' , gen(merge_bgatt)

keep if inlist(merge_bgatt , 1 , 3)

merge m:1 homeschl cbgpre linkpre using `sixbycbg_hs' , gen(merge_cbgprice)

keep if inlist(merge_cbgprice , 1 , 3)

merge m:1 home_lead1 cbg linkatt using `sixbycbg_hsld' , gen(merge_cbgpriceld)

keep if inlist(merge_cbgpriceld , 1 , 3)

merge m:1 home_lead2 cbg2 linkatt2 using `sixbycbg_hsld2' , gen(merge_cbgpriceld2)

keep if inlist(merge_cbgpriceld2 , 1 , 3)

gen mid_price_resid_diff = Mid_price_resid_hsld - Mid_price_resid_hs
gen midbyCBG_price_resid_diff = MidbyCBG_price_resid_hsld - MidbyCBG_price_resid_hs
gen mid_price_residV2_diff = Mid_price_residV2_hsld - Mid_price_residV2_hs
gen midbyCBG_price_residV2_diff = MidbyCBG_price_residV2_hsld - MidbyCBG_price_residV2_hs

gen mid_price_resid_diff2 = Mid_price_resid_hsld2 - Mid_price_resid_hs
gen midbyCBG_price_resid_diff2 = MidbyCBG_price_resid_hsld2 - MidbyCBG_price_resid_hs
gen mid_price_residV2_diff2 = Mid_price_residV2_hsld2 - Mid_price_residV2_hs
gen midbyCBG_price_residV2_diff2 = MidbyCBG_price_residV2_hsld2 - MidbyCBG_price_residV2_hs


** merge price resids

merge m:1 homeschl cbgpre linkpre year using `sixbycbgch_hs' , gen(merge_cbgpricech)

keep if inlist(merge_cbgpricech , 1 , 3)

merge m:1 home_lead1 cbg linkatt year using `sixbycbgch_hsld' , gen(merge_cbgpriceldch)

keep if inlist(merge_cbgpriceldch , 1 , 3)


***

egen yrhs = group(year homeschl)

qui xtreg mathstand_as wonfirst $lotvars i.year#homeschl , fe cluster(lottery) 
gen scoresample = e(sample) 

qui xtreg mathstand_at wonfirst $lotvars i.year#homeschl , fe cluster(lottery) 
gen atscoresample = e(sample)  

qui xtreg mid_price_resid_diff wonfirst $lotvars i.yrhs , fe cluster(lottery)
gen pricesample = e(sample) 

gen attriter = (scoresample==0 | atscoresample==0 | pricesample==0) 
gen stayer = (scoresample==1 & atscoresample==1 & pricesample==1)  

tempfile sixanalysis 
	save `sixanalysis' 
	
/* ******************************** append the two analysis files ************************** */

append using `kanalysis' 

keep if year<2013

save all-lottery-movers-analysis.dta , replace

keep if stayer==1 

bysort lottery: egen probcheck = mean(wonfirst)
keep if (probcheck>0 & probcheck<1)

drop probcheck

save stay-lottery-movers-analysis.dta , replace 
	
use all-lottery-movers-analysis.dta, clear

egen lotsch = group(appch1 year grade homeschl)

bysort lotsch: egen probcheck = mean(wonfirst) 

keep if probcheck>0 & probcheck<1 
drop probcheck

xtset lotsch

save all-lotsch-movers-analysis.dta , replace 

keep if stayer==1 

bysort lotsch: egen probcheck = mean(wonfirst)
keep if (probcheck>0 & probcheck<1)

drop probcheck

xtset lotsch 

save stay-lotsch-movers-analysis.dta , replace 


	

